﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using Storage;
using Storage.EntityModels;
using System.Data.Linq;
using Stats.Objects;

namespace Stats
{
    /// <summary>
    /// This class contains the necessary methods to calculate the statistics about trips, and methods
    /// to retrieve trip attrributes from db.
    /// </summary>
    public class AuxTripMethods
    {
        
        #region trips - passengers methods

        /// <summary>
        /// calculates all the passenger who take part in a trip
        /// </summary>
        /// <param name="tripID">the ID of the trip</param>
        /// <returns>number of passengers in a trip</returns>
        public static int getTripPassengersCount(int tripID)
        {
            int tripPassengersCount = 0;
            int additionalSeats = 0;

            try
            {
                TripLoaDataContext db = StorageManager.Instance.Entities;

                ArrayList IDs = new ArrayList();

                var stageEntriesForTrip =
                    from stageEntry in db.Stages
                    where stageEntry.TripId == tripID
                    select stageEntry;

                foreach (var stageEntry in stageEntriesForTrip)
                {
                    var passengerEntries =
                        from passengerEntry in db.Users_Stages
                        where passengerEntry.StageId == stageEntry.Id
                        select passengerEntry;

                    foreach (var passengerEntry in passengerEntries)
                    {
                        if (!IDs.Contains((int)passengerEntry.UserId)) { IDs.Add((int)passengerEntry.UserId); additionalSeats += passengerEntry.RequestedSeats -1; }
                    }
                    
                }

                tripPassengersCount = IDs.Count + additionalSeats; // total trip psn + add Seats
                    
            }
            catch (InvalidOperationException) { tripPassengersCount = 0; }
            catch (NullReferenceException) { tripPassengersCount = 0; }
            catch (System.Data.SqlClient.SqlException) { tripPassengersCount = 0; }

            return tripPassengersCount;

        }

        /// <summary>
        /// calculates the max number of busy seats in a trip
        /// </summary>
        /// <param name="tripID">the id of the trip</param>
        /// <returns>the number of busy seats</returns>
        public static int getTripMaxPassengers(int tripID)
        {
            int maxPSN = 0;

            try
            {
                TripLoaDataContext db = Storage.StorageManager.Instance.Entities;

                var stageEntriesForTrip =
                    from stageEntry in db.Stages
                    where stageEntry.TripId == tripID
                    select stageEntry;

                foreach (var stageEntry in stageEntriesForTrip)
                {
                    if (maxPSN < stageEntry.BusySeats) maxPSN = stageEntry.BusySeats;
                }

            }
            catch (InvalidOperationException) { maxPSN = 0; }
            catch (NullReferenceException) { maxPSN = 0; }
            catch (System.Data.SqlClient.SqlException) { maxPSN = 0; }

            return maxPSN;
        }

        /// <summary>
        /// Calculates the number of passengers carried during the specified trip that
        /// don't start from the trip initial stage
        /// </summary>
        /// <param name="tripID"></param>
        /// <returns></returns>
        public static int getPassengersCarriedOnTheWayCountForTrip(int tripID)
        {
            int passengersCarriedOnTheWayForTripCount = 0;
            int additionalSeats = 0;

            try
            {
                TripLoaDataContext db = StorageManager.Instance.Entities;

                ArrayList usersFromOriginIDsList = new ArrayList();
                ArrayList usersCarriedOnTheWayIDsList = new ArrayList();

                var firstStageEntry =
                    from stageEntry in db.Stages
                    where stageEntry.TripId == tripID && stageEntry.Ordinal == 1
                    select stageEntry;

                var stageEntriesForTrip =
                    from stageEntry in db.Stages
                    where stageEntry.TripId == tripID
                    select stageEntry;

                if (stageEntriesForTrip.Count() == 1) { passengersCarriedOnTheWayForTripCount = 0; }

                else
                {
                    var firstSEntry = firstStageEntry.First();

                    var passengerEntriesFromOrigin =
                        from passengerEntry in db.Users_Stages
                        where passengerEntry.StageId == firstSEntry.Id
                        select passengerEntry;

                    foreach (var passengerEntry in passengerEntriesFromOrigin)
                    {
                        usersFromOriginIDsList.Add((int)passengerEntry.UserId);
                    }

                    foreach (var stageEntry in stageEntriesForTrip)
                    {
                        var passengerEntries =
                            from passengerEntry in db.Users_Stages
                            where passengerEntry.StageId == stageEntry.Id
                            select passengerEntry;

                        foreach (var passengerEntry in passengerEntries)
                        {
                            if ((!usersFromOriginIDsList.Contains((int)passengerEntry.UserId)) && (!usersCarriedOnTheWayIDsList.Contains((int)passengerEntry.UserId))) { usersCarriedOnTheWayIDsList.Add((int)passengerEntry.UserId); additionalSeats += passengerEntry.RequestedSeats -1; }
                        }

                    }

                    passengersCarriedOnTheWayForTripCount = usersCarriedOnTheWayIDsList.Count + additionalSeats;

                }
            }
            catch (InvalidOperationException) { passengersCarriedOnTheWayForTripCount = 0; }
            catch (NullReferenceException) { passengersCarriedOnTheWayForTripCount = 0; }
            catch (System.Data.SqlClient.SqlException) { passengersCarriedOnTheWayForTripCount = 0; }

            return passengersCarriedOnTheWayForTripCount;
        }

        #endregion
 
        #region trips - utils methods

        /// <summary>
        /// returns the departure date of a trip in a nullable DateTime format
        /// </summary>
        /// <param name="tripID">the trip id</param>
        /// <returns>the departure date</returns>
        public static DateTime? getTripDepartureDate(int tripID)
        {
            DateTime? tripDepartureDate = null;

            try
            {
                TripLoaDataContext db = StorageManager.Instance.Entities;

                tripDepartureDate =
                    (from tripEntries in db.Trips
                     where tripEntries.Id == tripID
                     select tripEntries.DepartureDate).First();
            }
            catch (InvalidOperationException) { tripDepartureDate = null; }
            catch (NullReferenceException) { tripDepartureDate = null; }
            catch (System.Data.SqlClient.SqlException) { tripDepartureDate = null; }

            return tripDepartureDate;

        }
        
        /// <summary>
        /// return the id of a trip owner
        /// </summary>
        /// <param name="tripID">the trip id</param>
        /// <returns>trip owner's id</returns>
        public static int? getTripOwnerID(int tripID)
        {
            int? tripOwnerID = null;

            try
            {
                TripLoaDataContext db = StorageManager.Instance.Entities;

                tripOwnerID =
                    (from tripEntry in db.Trips
                     where tripEntry.Id == tripID
                     select tripEntry.OwnerId).First();
            }
            catch (InvalidOperationException) { tripOwnerID = null; }
            catch (NullReferenceException) { tripOwnerID = null; }
            catch (System.Data.SqlClient.SqlException) { tripOwnerID = null; }

            return tripOwnerID;
        }

        /// <summary>
        /// calculate an average of the free seats in a trip
        /// </summary>
        /// <param name="tripID">the id of the trip</param>
        /// <returns>the average of free seats</returns>
        public static double getTripAVGFreeSeats(int tripID)
        {
            double tripAVGFreeSeats = 0;

            try
            {
                TripLoaDataContext db = StorageManager.Instance.Entities;

                var tripFreeSeats =
                    (from tripEntry in db.Trips
                     where tripEntry.Id == tripID
                     select tripEntry.FreeSeats).First();

                if (tripFreeSeats == null) tripFreeSeats = 4;   // fre

                var busySeatsEntries =
                     from stageEntry in db.Stages
                     where stageEntry.TripId == tripID
                     select stageEntry.BusySeats;

                int stagesCount = busySeatsEntries.Count();
                int totalBusySeats = 0;

                foreach (var busySeatsCount in busySeatsEntries)
                {
                    totalBusySeats += busySeatsCount;
                }

                tripAVGFreeSeats = (double)tripFreeSeats - (totalBusySeats / stagesCount);
            }
            catch (InvalidOperationException) { tripAVGFreeSeats = 0; }
            catch (NullReferenceException) { tripAVGFreeSeats = 0; }
            catch (System.Data.SqlClient.SqlException) { tripAVGFreeSeats = 0; }

            return tripAVGFreeSeats;
        }

        /// <summary>
        /// This method returns all the attributes of a single trip like time and place in a TripProperties object
        /// </summary>
        /// <param name="tripID">The ID of a trip in the DB</param>
        /// <returns>A TripProperties object with all the attributes of the tripID trip</returns>
        public static TripProperties getTripProperties(int tripID)
        {
            TripProperties tripInfo = null;

            double busySeatsCount = 0;
            double avgFreeSeats = 0;
            double percentAvgFreeSeats = 0;
            bool expiredFlag = false;

            try
            {
                TripLoaDataContext db = Storage.StorageManager.Instance.Entities;

                var selectedTrip =
                    (from tripEntry in db.Trips
                     where tripEntry.Id == tripID
                     select tripEntry).First();

                int tripFreeSeats = (int)selectedTrip.FreeSeats;

                // if the user in trip creation insert an invalid number for free seats..here calcs are done with a normal car ( 4 seats for psn + 1)

                if (tripFreeSeats < 0 || tripFreeSeats == 0) { tripFreeSeats = 4; }
                
                // Obtaining first stage of the trip

                var firstStage =
                         (from stageEntry in db.Stages
                          where stageEntry.TripId == tripID && stageEntry.Ordinal == 1
                          select stageEntry).First();

                // Calculates the stages count for this trip

                var stagesCountForThisTrip = (int?)
                        (from stageEntry in db.Stages
                         where stageEntry.TripId == tripID
                         select stageEntry.Ordinal).Max();

                Place tripStartPlace = (Place)firstStage.Origin;

                Place tripDestinationPlace;

                //we have now two different behaviour, depending on how many stages there are in a trip

                //1 - single stage trip

                if (stagesCountForThisTrip == 1)
                {
                    tripDestinationPlace = (Place)firstStage.Destination;
                    avgFreeSeats = tripFreeSeats - firstStage.BusySeats;
                }

                // 2- multiple stages trip

                else
                {
                    var destPlaceEntries =
                        from stageEntry in db.Stages
                        where (stageEntry.TripId == tripID && stageEntry.Ordinal == stagesCountForThisTrip)
                        select stageEntry;

                    tripDestinationPlace = (Place)destPlaceEntries.First().Destination;

                    var stagesBusySeats =
                         from stageEntry in db.Stages
                         where stageEntry.TripId == tripID
                         select stageEntry.BusySeats;

                    foreach (var stageBusySeats in stagesBusySeats)
                    {
                        busySeatsCount += stageBusySeats;
                    }

                    avgFreeSeats = tripFreeSeats - (double)(busySeatsCount / stagesCountForThisTrip);

                }

                percentAvgFreeSeats = Math.Floor((avgFreeSeats / tripFreeSeats) * 100);

                if (DateTime.Today > selectedTrip.ReservationExpirationDate) expiredFlag = true;


                //here creates the object with the trip properties

                tripInfo = new TripProperties(selectedTrip.Id, (DateTime)selectedTrip.DepartureDate, tripStartPlace.City, tripStartPlace.Address, tripDestinationPlace.City, tripDestinationPlace.Address, percentAvgFreeSeats, expiredFlag);

            }
            catch (InvalidOperationException) { tripInfo = null; }
            catch (NullReferenceException) { tripInfo = null; }
            catch (System.Data.SqlClient.SqlException) { tripInfo = null; }

            return tripInfo;

        }

        #endregion


        

        
    }
}
